
let db = require('../db');

exports.getList = async function (villageId) {
    let sql=`
    SELECT 
    n.id,
   n.villageId,
   n.title,
   n.content,
   n.img,
   n.noticeType,
    DATE_FORMAT(n.createdAt,'%Y-%m-%d %H:%i') AS createdAt,
    vi.name AS villageName
   FROM 
   (SELECT *FROM notice WHERE isdelete='n') AS n
   LEFT JOIN village  AS vi ON vi.id  = n.villageId
    `
    if(villageId && villageId!=0){
        sql+= ` where n.villageId =:villageId `
    }
    
    sql+= ' ORDER BY n.createdAt  DESC'
    let data  =await db.query(sql,{villageId});
    return data[0];
}

//添加通知公告

exports.addNotice  = async function(villageId,title,noticeType,content,img){
let sql=`
INSERT  INTO notice(villageId,title,content,img,noticeType)
VALUES(:villageId,:title,:content,:img,:noticeType)
`
let  data  =await db.query(sql,{villageId,title,content,img,noticeType});
return data[0];
}

//获取单个通知公告的详情

exports.noticeDetail  = async function(noticeId){
let sql=`
SELECT  *,
DATE_FORMAT(createdAt,'%Y-%m-%d %H:%i') AS createdAt
FROM  notice WHERE id =:noticeId AND  isdelete='n'

`
let data  = await db.query(sql,{noticeId});
return data[0];
}

//删除通知公告

exports.removeNoitce = async function(noticeId){
    let sql=`
    UPDATE  notice SET 
    isdelete ='y'
    where id=:noticeId
    `
 let data =await db.query(sql,{noticeId});
 return data[0];   
}

//修改通知公告内容

exports.updateNotice = async function(id,villageId,title,noticeType,content,img){
let sql=`
UPDATE  notice SET 
villageId =:villageId,
title=:title,
noticeType =:noticeType,
content =:content,
img=:img
where id=:id

`
let data  =await db.query(sql,{id,villageId,title,noticeType,content,img});
return data[0];
}

//获取该小区下的所有设备
exports.getEquipmentByVillageId  = async function(villageId){
    let sql=`
     select SerialNum,mac  From equipment  Where isdelete ='n' AND  villageId = :villageId

    `
    let  data  =  await db.query(sql,{villageId});
    return data[0];
}